const Router = require('koa-router');
const router = new Router();
const {db, QueryTypes, } = require('../../models/db')
const tools = require("../../tools/tool");
const fs = require("fs");



router.post('/design/tableToMod', async ctx => {
    let tableName = ctx.request.body.tableName
    if(tableName) {
        let sql = `
        SELECT 
            表名       = d.name,
            表说明     = isnull(f.value,''),
            字段序号   = a.colorder,
            字段名     = a.name,
            标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 1 else 0 end,
            主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                             SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end,
            类型       = b.name,
            占用字节数 = a.length,
            长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
            小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
            允许空     = case when a.isnullable=1 then 1 else 0 end,
            默认值     = isnull(e.text,''),
            字段说明   = isnull(g.[value],'')
        FROM 
            syscolumns a
        left join 
            systypes b 
        on 
            a.xusertype=b.xusertype
        inner join 
            sysobjects d 
        on 
            a.id=d.id  and d.xtype in('U','V')  and  d.name<>'dtproperties'
        left join 
            syscomments e 
        on 
            a.cdefault=e.id
        left join 
        sys.extended_properties   g 
        on 
            a.id=G.major_id and a.colid=g.minor_id  
        left join
        sys.extended_properties f
        on 
            d.id=f.major_id and f.minor_id=0
        where 
            d.name='${tableName}' 
        order by 
            a.id,a.colorder
        `
        let list = await db.query(sql, {type: QueryTypes.SELECT})

        // indexes: [
        //     {unique: true, name: 'xx', fields:['tenantId', 'name']}
        // ]
        let indexArr = []
        let indexs = `exec sp_helpindex ${tableName}`
        let indexModel = await db.query(indexs, {type: QueryTypes.SELECT})
        for (let i = 0; i < indexModel.length; i++) {
            let ind = indexModel[i]
            if(ind.index_description.indexOf('unique key ') !== -1) {
                let indFields = ind.index_keys.split(',')
                indexArr.push({
                    unique: true,
                    name: ind.index_name,
                    fields: indFields
                })
            }
        }
        return saveMod(list, ctx, indexArr)
    }
})


const saveMod = (list, ctx, indexArr) => {
    let ind = indexArr.length > 0 ? ('indexes:'+JSON.stringify(indexArr)): ''
    let tableName = ctx.request.body.tableName
    let modName = tools.xe.camelCase(list[0].表名.replace(/_/g, '-'))
    let modComent = list[0].表说明
    try {
        let fieldStr = ''
        let idstr = ``
        let transC = 0
        list.map(row => {
            if(row.字段名 === 'id') {
                transC++
                idstr = `id: {type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true,comment:\'表自增ID\'},`
            }
            else {
                let an = ''
                let defaultValue = ''
                let dt = ''
                if(row.允许空 === 0) {
                    an = 'allowNull:false,'
                }
                if(row.类型 === 'varchar') {
                    if(row.长度 === -1) {
                        dt = `TEXT`
                    }
                    else {
                        dt = `STRING(${row.长度})`
                    }
                    transC++
                }
                if(row.类型 === 'nvarchar') {
                    if(row.长度 === -1) {
                        dt = `TEXT`
                    }
                    else {
                        dt = `STRING(${row.长度})`
                    }
                    transC++
                }
                if(row.类型 === 'text') {
                    dt = 'TEXT'
                    transC++
                }
                if(row.类型 === 'int') {
                    dt = 'INTEGER'
                    transC++
                }
                if(row.类型 === 'float') {
                    dt = 'FLOAT'
                    transC++
                }
                if(row.类型 === 'tinyint') {
                    dt = 'TINYINT'
                    transC++
                }
                if(row.类型 === 'smallint') {
                    dt = 'SMALLINT'
                    transC++
                }
                if(row.类型 === 'decimal') {
                    dt = `DECIMAL(${row.长度}, ${row.小数位数})`
                    transC++
                }
                if(row.类型 === 'datetime') {
                    dt = `DATE`
                    transC++
                }

                if(row.默认值) {
                    if(row.默认值 !== '(getdate())') {
                        if(row.默认值 !== '(NULL)')
                            defaultValue = 'defaultValue:' + row.默认值.replace('((','').replace('))','')+','
                    }
                }
                if(dt) {
                    let str = `${row.字段名}:{type: DataTypes.${dt},${an}${defaultValue}comment:'${row.字段说明}'}, \n`
                    fieldStr = fieldStr + str
                }
            }
        })
        if(transC !== list.length) {
            return ctx.err('要转列数量不正确')
        }

        let tb = `// ${modComent}
const {DataTypes, db } = require("../db");
db.define('${modName}', {
${idstr}
${fieldStr}
}, {
    tableName: '${tableName}',
    timestamps: false,
    comment: '${modComent}',
    ${ind}
});`
        saveFile(modName, tb)
        return ctx.err('转换成功')
    }
    catch (e) {
        return ctx.err('转换失败')
    }
}


const saveFile =  (fileName, data) => {
    fs.writeFile('./models/tables/'+fileName+'.js', data, err => {
        if(err) {
        }
        else {
        }
    })
}


module.exports = router;
